package demo;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Struct;
import java.sql.Types;
import java.text.DateFormat;
import java.util.Locale;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;

public class DemoOracleFunction {
	public final static String URL = "jdbc:oracle:thin:dv/dv@192.168.7.250:1521:XE";
	
	public static void main(String[] args) throws Exception{
		OracleDataSource ods = new OracleDataSource(); 
		ods.setURL(URL);
		Connection con = null;
		ResultSet rs = null;
		
		con = ods.getConnection();
		CallableStatement cs = null;
		String call = "{? = call getdate(?)}";
		try {
			cs = con.prepareCall(call);
			cs.registerOutParameter(1, Types.NVARCHAR);
			cs.setDate(2, new Date(System.currentTimeMillis()));
			cs.execute();
			String asd = cs.getString(1);
			System.out.println(asd);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			cs.close();
			con.close();
		}
		
		con = ods.getConnection();
		call = "{? = call asd()}";
		try {
			cs = con.prepareCall(call);
			cs.registerOutParameter(1, OracleTypes.CURSOR);
			cs.execute();
			rs = (ResultSet) cs.getObject(1);
//			System.out.println(rs.getMetaData().getColumnName(1));
			Locale locale = new Locale("fr", "FR");
			DateFormat df = DateFormat.getDateInstance(DateFormat.DATE_FIELD, locale);
			while (rs.next()) {
				String title = rs.getString(1);
				String author = rs.getString(2);
				String patron = rs.getString(3);
				Date date = rs.getDate(4);
				String sDate = df.format(date);
				System.out.println("title: " + title + "; author: " + author + "; patron: " + patron + "; date: " + sDate);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			cs.close();
			con.close();
		}
		
		
		con = ods.getConnection();
		call = "{? = call zxc()}";
		try {
			cs = con.prepareCall(call);
			cs.registerOutParameter(1, Types.ARRAY, "ASD_TYPE_R");
			cs.execute();	
			
			Object[] data = (Object[]) ((Array) cs.getObject(1)).getArray();
			for(Object tmp : data) {
				Struct row = (Struct) tmp;
				// Attributes are index 1 based...
				for(Object attribute : row.getAttributes()) {				
					System.out.println(attribute);											
				}
				System.out.println("---");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			cs.close();
			con.close();
		}
	}
}
